import MySQLdb

class DbClass:
  def __init__(self, schema, user='root', passwd='root', ip='localhost', cc='utf8'):
    self.schema =schema
    self.user = user
    self.passwd = passwd
    self.ip = ip
    self.cc=cc

  def open(self):
    db = MySQLdb.connect(
       host=self.ip
      ,user=self.user
      ,passwd=self.passwd
      ,db=self.schema
      ,charset=self.cc
    )
    #self.cursor = db.cursor()
    self.db=db

  def shutdown(self):
    self.db.close()

  def qryMeta(self):
    sql0 = "select t.`table_name`, t.table_comment from information_schema.`tables` t where t.table_schema = '%s'" % (self.schema)
    sql1 = "select c.`table_name`\
  ,c.`column_name`, c.column_type, c.is_nullable, c.column_default, c.column_comment, c.column_key \
from information_schema.`columns` c \
where c.table_schema = '{}' and c.`table_name` = '{}'"
    sql2="select s.table_name\
  ,s.`column_name`, s.index_name, s.seq_in_index, s.NON_UNIQUE \
from information_schema.statistics s \
where s.table_schema = '{0}' and s.`table_name` = '{1}'"
    sql3="select tc.`table_name`, tc.`constraint_name`, tc.constraint_type \
from information_schema.table_constraints tc \
where tc.table_schema = '{schma}' and tc.`table_name` = '{tblnm}'"

    listTbl=[]
    dictTbl={}
    dictCol={}
    dictSts={}
    dictCst={}
    try:
      cursor = self.db.cursor()
      cursor.execute(sql0)
      results = cursor.fetchall()
      for row in results:
        dictTbl[row[0]]=row[1]
        listTbl.append(row[0])

      for key, value in dictTbl.items():
        sql = sql1.format(self.schema, key)
        cursor.execute(sql)
        results = cursor.fetchall()
        colLst=[]
        for row in results:
          col={}
          col['tblnm']=key
          col['colnm']=row[1]
          col['ctype']=row[2]
          col['nable']=row[3]
          col['deflt']=row[4]
          col['commt']=row[5]
          col['colky']=row[6]
          colLst.append(col)
        dictCol[key]=colLst

        sql = sql2.format(self.schema, key)
        cursor.execute(sql)
        results = cursor.fetchall()
        colstsMap={}
        for row in results:
          colsts={} 
          colsts['tblnm']=key
          colsts['colnm']=row[1]
          colsts['idxnm']=row[2]
          colsts['idxsq']=row[3]
          colsts['nuniq']=row[4]
          if not row[1] in colstsMap:
            colstsMap[row[1]] = []
          colstsMap[row[1]].append(colsts)
        dictSts[key]=colstsMap

        sql = sql3.format(schma=self.schema, tblnm=key)
        cursor.execute(sql)
        results = cursor.fetchall()
        colcstMap={}
        for row in results:
          colcst={} 
          colcst['cstnm']=row[1]
          colcst['csttp']=row[2]
          if not row[1] in colcstMap:
            colcstMap[row[1]]=[]
            colcstMap[row[1]].append(colcst)
        dictCst[key]=colcstMap

    except:
      print("Error: unable to fetch data")

    return listTbl, dictTbl, dictCol, dictSts, dictCst

